create user SZY identified by "318675";
grant sysdba to SZY;
grant unlimited tablespace to SZY;
--删除所有表
drop table SYS_TASK;
drop table SYS_PERSON;
drop table SYS_FEEDBACK;
drop table SYS_COMPLAINTS;
drop table SYS_USER;
--建表

-- 用户表
create table SYS_USER
(
    ID      NUMBER           not null
        primary key,
    NAME    VARCHAR2(20)     not null,
    SEX     INT           not null,
    PWD     VARCHAR2(30)     not null,
    EMAIL   varchar2(40) default null,
    ROLE    INT default 0 not null,
    USED    INT default 0 not null,
    PHONE NUMBER,
    STCARD varchar2(4000)  null,
    FACE varchar2(4000) null
);
-- alter table SYS_USER
--     add FACE varchar2(4000)  null;
-- 任务表
create table SYS_TASK(
    T_ID number primary key not null ,--任务编号
    ID  number not null
        constraint SYS_TASK_SYS_USER_ID_FK
        references SYS_USER,
    SERVICE varchar2(20) not null,
    ADDRESS varchar2(20) not null,
    TIME DATE ,
    STATE number default 1 not null,
    JG number not null ,
    PHONE number not null,
    ACCEPT number null ,
    END_TIME DATE
);
--个人信息表
create table SYS_PERSON(
    ID   number not null
    constraint SYS_PERSON_SYS_USER_ID_FK
    references SYS_USER,
    P_PHONE number,
    MAJOR varchar2(50),
    DORM varchar2(10),
    GRADE varchar2(10),
    SCHOOL varchar2(50)
);
-- 反馈表
create table SYS_FEEDBACK(
     FEEDBACK_ID  number primary key not null ,
     ID number not null
     constraint SYS_FEEDBACK_SYS_USER_ID_FK
     references SYS_USER,
     CONTENTS  VARCHAR2(4000)
);
-- 投诉表
create table SYS_COMPLAINTS(
     COMPLAINTS_ID number primary key not null,
     ID number not null
     constraint  SYS_COMPLAINTS_SYS_USER_ID_FK
     references SYS_USER,
     TYPE varchar2(5) not null,
     REASON varchar2(4000),
     Complained number
);
--创建任务表序列
create sequence Task_sequence
increment by 180
start with 2021042035
nomaxvalue
minvalue 1
nocycle
nocache
order ;
-- 创建投诉表序列
create sequence Complaints_sequence
    increment by 1
    start with 1
    nomaxvalue
    minvalue 1
    nocycle
    nocache
    order ;
-- 创建反馈表序列
create sequence Feedback_sequence
    increment by 1
    start with 1
    nomaxvalue
    minvalue 1
    nocycle
    nocache
    order ;
--创建触发器，每次插入SYS_TASK表主键自增
create or replace trigger Task_trigger
    before insert on SYS_TASK for each row
    WHEN ( NEW.T_ID is null )
    begin
    select Task_sequence.nextval INTO:NEW.T_ID from dual;
end;

--创建触发器，每次插入SYS_COMPLAINTS表主键自增
create or replace trigger COMPLAINTS_trigger
    before insert on SYS_COMPLAINTS for each row
    WHEN ( NEW.COMPLAINTS_ID is null )
begin
    select Complaints_sequence.nextval INTO:NEW.COMPLAINTS_ID from dual;
end;

--创建触发器，每次插入SYS_Feedback表主键自增
create or replace trigger Feedback_trigger
    before insert on SYS_FEEDBACK for each row
    WHEN ( NEW.FEEDBACK_ID is null )
begin
    select Complaints_sequence.nextval INTO:NEW.FEEDBACK_ID from dual;
end;
-- alter table SYS_COMPLAINTS
--     add C_ID number not null;
--存在则更新，不存在则插入
-- merge into SYS_PERSON t1 USING dual on ((select COUNT(*) from SYS_PERSON where SYS_PERSON.ID=2)>0  )
-- when matched then
-- update set t1.P_PHONE=19892072935, t1.MAJOR=126,t1.DORM='计算机',t1.GRADE='2020',t1.SCHOOL='中南林科大涉外学院' where ID=3
-- when not matched then  insert (ID,p_phone, dorm, grade, school) VALUES (3, 123456,'1-504','2020','中南林科大涉外学院' );
-- insert into SYS_PERSON(id, p_phone, major, dorm, grade, school) VALUES (1,19892072935,'计算机科学与技术','1-504','2019','涉外学院')
-- select COUNT(*) from SYS_TASK;
--select rownum,s.* from (select  rownum r ,s.* from SYS_TASK s where rownum<=6) s where r>= 1*3;
-- select rownum,s.* from (select  rownum r ,s.* from SYS_USER s
--                         where rownum <=  1 * (1 - 1)) s where r > 1*2;
-- delete from SYS_TASK where ID=1;
-- select *from SYS_USER,SYS_DEPT where SYS_USER.DEPT_ID=SYS_DEPT.DEPT_ID and SYS_DEPT.DEPT_ID=2;
-- select *from SYS_USER;
--  select *from SYS_TASK;
-- select SYS_USER.ID,name,SERVICE,ADDRESS,TIME,STATE from SYS_USER,SYS_TASK where SYS_USER.ID=SYS_TASK.ID and SYS_USER.ID=7;
-- -- delete from SYS_TASK where T_ID=2;

commit;
